package com.ynny.spring.student.systemDB.dao;

import com.ynny.spring.student.systemDB.bean.Score;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import java.util.List;

@Component("scoreManage")
public class ScoreManageImpl implements ScoreManage {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public void addScore(Score score) {
        // 定义SQL插入语句，实现插入一条学生成绩记录
        String sql = "insert into scores(number, Chinese, math, English) values(?, ?, ?, ?)";
        // 定义数组来存放SQL语句中的参数
        Object[] obj = new Object[]{
                score.getNumber(),
                score.getChinese(),
                score.getMath(),
                score.getEnglish()
        };
        // 执行SQL插入语句，返回执行添加操作的记录数
        int num = jdbcTemplate.update(sql, obj);
        if (num <= 0)
            System.out.println("插入学生成绩记录失败");
    }

    @Override
    public void updateScore(String number, String course, Double score) {
        // 定义SQL更新语句，实现根据学号更新课程对应的成绩
        String sql = "update scores set " + course + " = ? where number = ?";

        // 定义数组来存放SQL语句中的参数
        Object[] params = new Object[]{score, number};
        int num = jdbcTemplate.update(sql, params);
        if (num <= 0)
            System.out.println("更新学生成绩记录失败");
    }

    @Override
    public void queryScoreByName(String number) {
        // 定义SQL查询语句，实现根据学号查询单个学生成绩记录
        String sql = "select * from scores where number = ?";
        // 创建一个BeanPropertyRowMapper对象
        org.springframework.jdbc.core.RowMapper<Score> rowMapper = new org.springframework.jdbc.core.BeanPropertyRowMapper<>(Score.class);
        // 执行SQL查询语句，通过rowMapper返回Score类对象
        Score score = jdbcTemplate.queryForObject(sql, rowMapper, number);
        System.out.println(score);
    }

    @Override
    public void queryAllScore() {
        // 定义SQL查询语句，实现查询所有学生成绩记录
        String sql = "select * from scores";
        // 创建一个BeanPropertyRowMapper对象
        org.springframework.jdbc.core.RowMapper<Score> rowMapper = new org.springframework.jdbc.core.BeanPropertyRowMapper<>(Score.class);
        // 执行SQL查询语句，通过rowMapper返回存放Score类的对象的List集合
        List<Score> scores = jdbcTemplate.query(sql, rowMapper);
        for (Score score : scores) {
            System.out.println(score);
        }
    }
}
